SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


CREATE Proc [dbo].[SP_TXEXTR_BANC_U01]      
/*---------------------------------------------------------------------*/      
/*----- Empresa          : OFISIS S.A.                            -----*/      
/*----- Cliente          : OFISIS S.A.                            -----*/      
/*----- Sistema          : Contabilidad                           -----*/      
/*----- Modulo           : Empresa                                -----*/      
/*----- Programa         : Proceso de Conciliacion Bancaria       -----*/      
/*----- Nombre           : SP_TXEXTR_BANC_U01                     -----*/      
/*----- Script           : TXEXTR_BANC                            -----*/      
/*----- Desarrollado por : Martin Garcia Farfan                   -----*/      
/*----- Fecha            : 23/10/2002                             -----*/      
/*----- Base de Datos    : MS SQL Server                          -----*/      
/*----- Version BD       : 7                                      -----*/      
/*----- Invoca a SP      :                                        -----*/      
/*---------------------------------------------------------------------*/      
/*----- MODIFICADO 1     : PDGV                                   -----*/    
/*----- FECHA MODIF      : 10/05/2004                             -----*/    
/*----- DETALE MODIF     : LLAMA A PROCEDURE QUE U03 PARA RETORNAR ST_FLAG --> 'N' ----*/    
/*----- Y NU_ANNO_CONS / NU_MESE_CONS ---> NULL                   -----*/     
/*---------------------------------------------------------------------*/      
/*---------------------------------------------------------------------*/    
/*----- Modificado 2 por : percy garcia                           -----*/    
/*----- Fecha Modifica   : 05/06/2004                             -----*/    
/*----- Detalle Modifica : usuario creacion y modif               -----*/    
/*---------------------------------------------------------------------*/   
/*---------------------------------------------------------------------*/   
/*----- Drop Proc SP_TXEXTR_BANC_U01                              -----*/      
/*----- Grant Execute On SP_TXEXTR_BANC_U01 To PUBLIC             -----*/      
/*----- SP_TXEXTR_BANC_U01 '01',1,1999,3, '10402'                 -----*/      
/*---------------------------------------------------------------------*/      
@ISCO_EMPR        TD_VC_002,
@ISCO_USUA        TD_VC_008,  
@IDFE_USUA        TD_DT_001,  
@INNU_CNTB_EMPR   TD_IN_001,      
@INNU_ANNO        TD_IN_004_ANNO,      
@INNU_MESE        TD_IN_002_MESE,      
@ISCO_CNTA_EMPR   TD_VC_020      
As      
Declare      
 @CSCO_CNTA_EMPR  TD_VC_020,      
 @CSCO_MONE       TD_VC_003,      
 @CNNU_ANNO       TD_IN_004_ANNO,      
 @CNNU_MESE       TD_IN_002_MESE,      
 @CSTI_DOCU       TD_VC_003,      
 @CSNU_DOCU       TD_VC_020,        
 @CNIM_MVTO_DOCU  TD_NU_016_004,      
 @CSTI_OPER       TD_VC_003,      
 @VNNU_TOTA       TD_IN_001      
       
 Begin Tran SP_TXEXTR_BANC_U01      
    
 Exec SP_TXEXTR_BANC_U03 @ISCO_EMPR, @INNU_CNTB_EMPR, @INNU_ANNO, @INNU_MESE, @ISCO_CNTA_EMPR     
    
 Declare CU_TXEXTR_BANC_U01 cursor      
  for      
   Select CO_CNTA_EMPR, CO_MONE, NU_ANNO, NU_MESE, TI_DOCU, NU_DOCU,      
     Isnull(sum(Isnull(IM_MVTO_DOCU, 0)), 0), TI_OPER      
   From TXMVTO_BANC      
   Where CO_EMPR      = @ISCO_EMPR       
   And   NU_CNTB_EMPR = @INNU_CNTB_EMPR       
   And   NU_ANNO = @INNU_ANNO       
   And   NU_MESE <= @INNU_MESE       
   And   LTRIM(RTRIM(CO_CNTA_EMPR)) = LTRIM(RTRIM(@ISCO_CNTA_EMPR ))      
   And   ST_FLAG = 'N'      
   Group by CO_CNTA_EMPR, CO_MONE, NU_ANNO, NU_MESE, TI_DOCU, NU_DOCU, TI_OPER      
   Order by CO_CNTA_EMPR, CO_MONE, NU_ANNO, NU_MESE, TI_DOCU, NU_DOCU, TI_OPER      
          
 Select @VNNU_TOTA = 0      
       
 open CU_TXEXTR_BANC_U01       
 Fetch CU_TXEXTR_BANC_U01 into @CSCO_CNTA_EMPR, @CSCO_MONE, @CNNU_ANNO,      
    @CNNU_MESE, @CSTI_DOCU, @CSNU_DOCU, @CNIM_MVTO_DOCU, @CSTI_OPER      
 While @@Fetch_Status = 0      
  Begin      
    Select @VNNU_TOTA = count(*)      
        from TXEXTR_BANC       
      where CO_EMPR      = @ISCO_EMPR and      
            NU_CNTB_EMPR = @INNU_CNTB_EMPR and      
            NU_ANNO   = @INNU_ANNO and      
            NU_MESE      <= @INNU_MESE and      
            LTRIM(RTRIM(CO_CNTA_EMPR)) = LTRIM(RTRIM(@CSCO_CNTA_EMPR)) and      
            LTRIM(RTRIM(TI_DOCU))      = LTRIM(RTRIM(@CSTI_DOCU)) and      
            LTRIM(RTRIM(NU_DOCU))      = LTRIM(RTRIM(@CSNU_DOCU)) and      
            LTRIM(RTRIM(CO_MONE))      = LTRIM(RTRIM(@CSCO_MONE)) And      
            IM_MVTO_DOCU = @CNIM_MVTO_DOCU And      
            LTRIM(RTRIM(TI_OPER))      = LTRIM(RTRIM(@CSTI_OPER))      
       
      
    If @VNNU_TOTA > 0       
      Begin      
            
        Update TXEXTR_BANC      
   Set   NU_ANNO_CONS = @INNU_ANNO,      
  NU_MESE_CONS = @INNU_MESE,      
  ST_FLAG      = 'S',      
  CO_USUA_MODI = @ISCO_USUA,      
  FE_USUA_MODI = @IDFE_USUA      
 Where CO_EMPR      = @ISCO_EMPR       
 And   NU_CNTB_EMPR = @INNU_CNTB_EMPR       
 And   NU_ANNO     = @INNU_ANNO       
 And   NU_MESE     <= @INNU_MESE       
 And   LTRIM(RTRIM(CO_CNTA_EMPR)) = LTRIM(RTRIM(@CSCO_CNTA_EMPR))       
 And   LTRIM(RTRIM(TI_DOCU))      = LTRIM(RTRIM(@CSTI_DOCU ))      
 And   LTRIM(RTRIM(NU_DOCU))      = LTRIM(RTRIM(@CSNU_DOCU ))      
 And   LTRIM(RTRIM(CO_MONE))      = LTRIM(RTRIM(@CSCO_MONE ))      
 And   IM_MVTO_DOCU = @CNIM_MVTO_DOCU       
 And   LTRIM(RTRIM(TI_OPER))      = LTRIM(RTRIM(@CSTI_OPER))      
        If @@ERROR != 0           
        Begin        
          Close CU_TXEXTR_BANC_U01      
          Deallocate CU_TXEXTR_BANC_U01      
          Rollback tran       
          Return      
        End      
        Update TXMVTO_BANC      
   Set   ST_FLAG      = 'S',      
                NU_ANNO_CONS = @INNU_ANNO,      
  NU_MESE_CONS = @INNU_MESE,      
  CO_USUA_MODI = @ISCO_USUA,      
  FE_USUA_MODI = @IDFE_USUA      
 Where CO_EMPR      = @ISCO_EMPR       
 And   NU_CNTB_EMPR = @INNU_CNTB_EMPR       
 And   NU_ANNO     = @INNU_ANNO       
 And   NU_MESE     <= @INNU_MESE       
 And   LTRIM(RTRIM(CO_CNTA_EMPR)) = LTRIM(RTRIM(@CSCO_CNTA_EMPR ))      
 And   LTRIM(RTRIM(TI_DOCU))      = LTRIM(RTRIM(@CSTI_DOCU ))      
 And   LTRIM(RTRIM(NU_DOCU))      = LTRIM(RTRIM(@CSNU_DOCU ))      
 And   LTRIM(RTRIM(CO_MONE))      = LTRIM(RTRIM(@CSCO_MONE ))      
 And   IM_MVTO_DOCU = @CNIM_MVTO_DOCU       
 And   LTRIM(RTRIM(TI_OPER))      = LTRIM(RTRIM(@CSTI_OPER))      
       If @@ERROR != 0           
        Begin       
         Close CU_TXEXTR_BANC_U01      
         Deallocate CU_TXEXTR_BANC_U01      
         Rollback tran       
         Return      
       End      
     End      
            
   Select @VNNU_TOTA = 0        
   Fetch CU_TXEXTR_BANC_U01 into @CSCO_CNTA_EMPR, @CSCO_MONE, @CNNU_ANNO,      
      @CNNU_MESE, @CSTI_DOCU, @CSNU_DOCU, @CNIM_MVTO_DOCU, @CSTI_OPER      
  End      
  close CU_TXEXTR_BANC_U01      
 deallocate CU_TXEXTR_BANC_U01      
 commit tran      
 return      
/*---------------------------------- Fin -----------------------------------*/      





GO
